﻿using System.Data.SQLite;
using System;
using System.IO;
using System.Diagnostics;
using CSharp.Libs.Koc.KernelModels.ItemClasses;
using System.Collections.ObjectModel;
using CSharp.Libs.Koc;


namespace CSharp.Libs.Koc.KernelModels
{
    public partial class DatabaseOperations
    {
        #region declarations
        private Kernel _AB = null;
        private SQLiteConnection _CON = null;
        private Stopwatch _SW = new Stopwatch();
        
        private String _strDatabaseFileName = "";
        private Boolean _fDbOpen = false;
        private Boolean _fTimeDiagnosis = false;

        #region events
        public delegate void LogEntryEventHandler(object sender, LogEntryEventArgs e);
        public delegate void TaskEventHandler(object sender, TaskEventArgs e);

        public event LogEntryEventHandler LogEntry;
        public event TaskEventHandler ehTask;

        private void ThrowLogEntryEvent(DateTime EntryDateTime, String Message)
        {
            if (LogEntry != null)
            {
                LogEntry(this, new LogEntryEventArgs(EntryDateTime, Message));
            }
        }
        private void ThrowLogEntryEvent(String Message)
        {
            if (LogEntry != null)
            {
                LogEntry(this, new LogEntryEventArgs(Message));
            }
        }

        private void ThrowTaskEvent(TaskEventArgs.enmTaskAction taTaskAction, String TaskMessage)
        {
            if (ehTask != null)
            {
                ehTask(this, new TaskEventArgs(taTaskAction, TaskMessage));
            }
        }
        #endregion
        #endregion



        #region constructor / terminator
        public DatabaseOperations(
            Kernel AB)
        {
            _AB = AB;
        }

        ~DatabaseOperations()
        {
            try
            {
                if (_SW.IsRunning)
                {
                    _SW.Stop();
                }
                _SW = null;

                if(_CON != null)
                {
                    _CON.Close();
                    _CON.Dispose();
                    _CON = null;
                }

                if (_AB != null)
                {
                    _AB = null;
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }
        }
        #endregion



        #region event handling
        #endregion



        #region private functions
        #region *   Database Creation
        /// <summary>_InitDB</summary>
        /// <history>10.04.2011</history>
        private void _InitDB(
            String DatabaseFileName)
        {
            try
            {
                _strDatabaseFileName = DatabaseFileName;

                if (!File.Exists(_strDatabaseFileName))
                {
                    _CreateDB();
                }

                if (File.Exists(_strDatabaseFileName))
                {
                    _OpenDB();
                }
                else
                {
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "Specified File couldn't be created.", 
                        _strDatabaseFileName);
                    _AB.EC.AddException(_exFileNotFound, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_CreateDB</summary>
        /// <history>10.04.2011</history>
        private void _CreateDB()
        {
            try
            {
                //Make sure DB does not exist
                if(!File.Exists(_strDatabaseFileName))
                {
                    using(SQLiteConnection CON = new SQLiteConnection(String.Concat("Data Source=", _strDatabaseFileName)))
                    {
                        CON.Open();

                        using(SQLiteCommand CMD = CON.CreateCommand())
                        {
                            CMD.CommandText = String.Concat(
                                "CREATE TABLE [tblAutoTransport] (",
                                "[AutoTransportID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,",
                                "[ServerID] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[CityID] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[TransportActive] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[DestinationXCoord] INTEGER DEFAULT '0' NULL,",
                                "[DestinationYCoord] INTEGER DEFAULT '0' NULL,",
                                "[TransportGold] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[TransportFood] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[TransportWood] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[TransportStone] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[TransportOre] INTEGER DEFAULT '''0''' NOT NULL,",
                                "[GoldMin] INTEGER DEFAULT '0' NOT NULL,",
                                "[GoldMax] INTEGER DEFAULT '0' NOT NULL,",
                                "[FoodMin] INTEGER DEFAULT '0' NOT NULL,",
                                "[FoodMax] INTEGER DEFAULT '0' NOT NULL,",
                                "[WoodMin] INTEGER DEFAULT '0' NOT NULL,",
                                "[WoodMax] INTEGER DEFAULT '0' NOT NULL,",
                                "[StoneMin] INTEGER DEFAULT '0' NOT NULL,",
                                "[StoneMax] INTEGER DEFAULT '0' NOT NULL,",
                                "[OreMin] INTEGER DEFAULT '0' NOT NULL,",
                                "[OreMax] INTEGER DEFAULT '0' NOT NULL",
                                ");",
                                Environment.NewLine,
                                "CREATE INDEX [idxAutoTransport] ON [tblAutoTransport](",
                                "[ServerID]  DESC,",
                                "[CityID]  DESC",
                                ");",
                                Environment.NewLine,
                                "CREATE TABLE [tblMarches] (",
                                "[MarchID] INTEGER  PRIMARY KEY NOT NULL, ",
                                "[TileID] INTEGER  NULL, ",
                                "[TileType] INTEGER  NULL, ",
                                "[TileLevel] INTEGER  NULL, ",
                                "[InitTS] INTEGER  NULL, ",
                                "[Distance] FLOAT  NULL, ",
                                "[Speed] INTEGER  NULL, ",
                                "[ETA] INTEGER  NULL, ",
                                "[Delay] INTEGER  NULL, ",
                                "[LiftFog] INTEGER  NULL, ",
                                "[AtkBoostTime] INTEGER  NULL, ",
                                "[Atk2BoostTime] INTEGER  NULL, ",
                                "[DefBoostTime] INTEGER  NULL, ",
                                "[Def2BoostTime] INTEGER  NULL, ",
                                "[KnightCombatBoostTime] INTEGER  NULL, ",
                                "[MarchUnixTime] INTEGER  NULL, ",
                                "[ReqMicroTime] FLOAT  NULL, ",
                                "[ResMicroTime] FLOAT  NULL, ",
                                "[ServerID] INTEGER  NULL, ",
                                "[CityID] INTEGER  NULL, ",
                                "[KnightID] INTEGER  NULL, ",
                                "[DestinationXCoord] INTEGER  NULL, ",
                                "[DestinationYCoord] INTEGER  NULL, ",
                                "[MarchType] INTEGER  NULL, ",
                                "[Gold] INTEGER  NULL, ",
                                "[R1] INTEGER  NULL, ",
                                "[R2] INTEGER  NULL, ",
                                "[R3] INTEGER  NULL, ",
                                "[R4] INTEGER  NULL, ",
                                "[Items] NVARCHAR(256)  NULL, ",
                                "[U1] INTEGER  NULL, ",
                                "[U2] INTEGER  NULL, ",
                                "[U3] INTEGER  NULL, ",
                                "[U4] INTEGER  NULL, ",
                                "[U5] INTEGER  NULL, ",
                                "[U6] INTEGER  NULL, ",
                                "[U7] INTEGER  NULL, ",
                                "[U8] INTEGER  NULL, ",
                                "[U9] INTEGER  NULL, ",
                                "[U10] INTEGER  NULL, ",
                                "[U11] INTEGER  NULL, ",
                                "[U12] INTEGER  NULL); ",
                                Environment.NewLine,
                                "CREATE INDEX [idxMarches] ON [tblMarches]( ",
                                "[MarchID]  ASC, ",
                                "[ETA]  ASC, ",
                                "[MarchUnixTime]  ASC, ",
                                "[InitTS]  ASC, ",
                                "[TileID]  ASC, ",
                                "[ServerID]  ASC, ",
                                "[CityID]  ASC, ",
                                "[DestinationXCoord]  ASC, ",
                                "[DestinationYCoord]  ASC, ",
                                "[KnightID]  ASC)");

                            try
                            {
                                CMD.ExecuteNonQuery();
                            }
                            catch(Exception ex)
                            {
                                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
                            }
                        }

                        CON.Close();
                    }
                }
                else
                {
                    _AB.EC.AddException(new FileLoadException("ERROR in DatabaseOperations._CreateDB: File already exists so I better do nothing. Otherwise an existing database could get overwritten completely.", _strDatabaseFileName), this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
                }
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_OpenDatabase</summary>
        /// <history>10.04.2011</history>
        private void _OpenDB()
        {
            try
            {
                if (File.Exists(_strDatabaseFileName))
                {
                    if (_fTimeDiagnosis)
                    {
                        _SW.Start();
                    }

                    //Setting Cache to 600MB for performance reasons
                    //Testphase
                    _CON = new SQLiteConnection(String.Concat(
                        "Data Source=", _strDatabaseFileName, ";",
                        "Cache Size=600000;",
                        "Page Size=1024"));
                    _CON.Open();
                    _fDbOpen = true;

                    if (_fTimeDiagnosis)
                    {
                        _SW.Stop();
                        ThrowLogEntryEvent(DateTime.Now, "TimeDiagnosis - _OpenDatabase (" + _strDatabaseFileName + ") - " + _SW.Elapsed.ToString());
                    }
                }
                else
                {
                    _fDbOpen = false;
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "ERROR opening SQLiteDB: File not found.",
                        _strDatabaseFileName);
                    _AB.EC.AddException(_exFileNotFound, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());

                }
            }
            catch (Exception ex)
            {
                _fDbOpen = false;
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            try
            {
                //_InitTableStuff();
                //_GetCatTables();
                //_GetTables();
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }
        }
        #endregion

        #region basic functions
        /// <summary>_GetBoolean</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Boolean _GetBoolean(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                Int64.TryParse(Input.ToString(), out RetVal);

                if(RetVal != 0)
                {
                    return true;
                }
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_GetInt64</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _GetInt64(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                Int64.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_GetDouble</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.05.2011</history>
        private Double _GetDouble(
            Object Input)
        {
            try
            {
                Double RetVal = -1;
                Double.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_SetBoolean
        /// 
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _SetBoolean(
            Boolean Input)
        {
            try
            {
                if(Input)
                {
                    return 1;
                }
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return 0;
        }

        /// <summary>_TableExists</summary>
        /// <param name="CON"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        /// <history>02.08.2011</history>
        private Boolean _TableExists(
            SQLiteConnection CON, 
            String strTableName)
        {
            SQLiteCommand CMD = new SQLiteCommand(CON);
            CMD.CommandText = String.Concat("SELECT name FROM sqlite_master WHERE name='", strTableName, "'");
            
            SQLiteDataReader DR = CMD.ExecuteReader();

            if (DR.HasRows)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #endregion



        #region interface
        #region properties
        public String DatabaseFileName
        {
            get
            {
                return _strDatabaseFileName;
            }
        }

        public Boolean DbOpen
        {
            get
            {
                return _fDbOpen;
            }
        }
        #endregion

        #region methods
        public void InitDb(
            String DatabaseFileName)
        {
            _InitDB(DatabaseFileName);
        }
        #endregion
        #endregion
    }
}
